package com.egoo.ticket.server.utils.common;

import com.alibaba.fastjson.JSONObject;
import com.egoo.ticket.server.dao.*;
import com.egoo.ticket.server.pojo.entity.*;
import com.egoo.ticket.server.pojo.vo.SysDictVo;
import com.egoo.ticket.server.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author：victor_tang
 * @Date: 2019/10/7
 * @Description: 过滤器，触发器动态sql条件拼接工具类
 */
@Component
@Slf4j
public class DynamicSqlJoinUtil {
	@Autowired
	private EwsUserDao ewsUserDao;
	@Autowired
	private EwsUserRoleDao ewsUserRoleDao;
	@Autowired
	private EwsDepartmentDao ewsDepartmentDao;
	@Autowired
	private FilterFirstDao filterFirstDao;
	@Autowired
	private SysDictDao sysDictDao;
	@Autowired
	private WorkOrderFilterMenuDao workOrderFilterMenuDao;
	@Autowired
	private CommonUtil commonUtil;
	@Autowired
	private UserService userService;


	/**
	 * TODO 拼接系统默认过滤器sql
	 * @param filterId 1
	 * @param userId   2
	 * @author: victor_tang
	 * @createtime: 2019/9/27 21:18
	 * @return: java.lang.String
	 */
	public String selectByDynamicSystem(Integer filterId, String filterName, String userId,String tenantId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select a.*,"+
				"b.work_order_id,b.urge_count,b.redeploy_count,b.contact_work_order_id_str,b.is_time_out,b.session_id,b.external_work_order_id,b.work_order_monitor_id,b.merge_base_work_order_ids,b.is_recieve,b.sender,b.is_children " +
				"from work_order a,work_order_relation b " +
				"where  a.id = b.work_order_id");
//		判断子工单展示是否启用
		sql = getSysDictStatus(ConstantCode.SysDictCode.SUB_ORDER_VIEW, sql);

		WorkOrderFilterMenu workOrderFilterMenu = workOrderFilterMenuDao.selectById(filterId,tenantId);
		String filterMenuDataType = workOrderFilterMenu.getFilterMenuDataType();
		String filterMenuDataTypeIds = workOrderFilterMenu.getFilterMenuDataTypeIds();
		// 所有工单列表
		if (ConstantCode.SysFilter.ALL_FILTER_NAME.equals(filterName)) {

			if (ConstantCode.UserRoleDeptType.ALL.equals(filterMenuDataType)){
				return sql.toString();
			}
			List<String> userIds = getUserIds(filterMenuDataType, filterMenuDataTypeIds);
			if(!CollectionUtils.isEmpty(userIds)&& userIds.contains(userId)){
				return sql.toString();
			}else {
				sql.append(" and ( a.create_user_id = '").append(userId)
					.append("' or ").append("find_in_set('").append(userId).append("',"+"a.current_handler_id)")
					.append(" or ").append("find_in_set('").append(userId).append("',"+"a.history_handler_id)")
					.append(")");
				return sql.toString();
			}

		}
		//编辑中工单
		else if (ConstantCode.SysFilter.EDIT_FILTER_NAME.equals(filterName)) {
			sql.append(" and a.create_user_id = '").append(userId).append("'")
					.append(" and a.status_id = ").append(ConstantCode.WorkOrderState.WAIT_EDIT);
			return sql.toString();
		}
		//我创建工单
		else if (ConstantCode.SysFilter.CREATE_FILTER_NAME.equals(filterName)) {
			sql.append(" and a.create_user_id = '").append(userId).append("'");
			return sql.toString();
		}
		//我关注的工单
		else if (ConstantCode.SysFilter.MONITOR_FILTER_NAME.equals(filterName)) {
			sql.append(" and find_in_set('").append(userId).append("'," +
					"b.work_order_monitor_id)");
			return sql.toString();
		}
		//待处理的工单
		else if (ConstantCode.SysFilter.WAIT_HANDLE_FILTER_NAME.equals(filterName)) {
			sql.append(" and (find_in_set('").append(userId).append("'," +
					"a.current_handler_id) or current_handler_id = '')")
					.append("and (a.status_id not in(")
					.append(ConstantCode.WorkOrderState.ALREDAY_CLOSE)
			.append(",").append(ConstantCode.WorkOrderState.ALREAAY_CASE)
			.append("))");
			return sql.toString();
		}
		//我完成的工单
		else if (ConstantCode.SysFilter.FINISHED_FILTER_NAME.equals(filterName)){
			sql.append(" and a.status_id in (3,4) and a.close_user_id = '").append(userId).append("'");
		}
		//我部门下的工单
		else if (ConstantCode.SysFilter.FROM_DEPT_FILTER_NAME.equals(filterName)){
//
			String deptIdSql = "(SELECT department_id FROM ews_user WHERE id = '" +userId+"')";
			String deptChildSql = "select id from (select t1.id,if(find_in_set(pid, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild " +
					"from (select id,pid from ews_department t where tenant_id = '"+tenantId+"') t1,(select @pids := "+deptIdSql+") t2 ) t3 where ischild != '0'";
			sql.append(" and a.create_user_id in (")
					.append(" SELECT d.id FROM ews_user d where d.tenant_id='"+tenantId+"' and ( d.department_id in(").append(deptChildSql).append(")")
					.append(" or ").append("d.department_id in(").append(deptIdSql).append("))")
					.append(")");
		}
		return sql.toString();
	}

	private List<String> getUserIds(String filterMenuDataType, String filterMenuDataTypeIds) {
		List<String> ids = null;
		if (ConstantCode.UserRoleDeptType.DEPT.equals(filterMenuDataType)){
			ids = commonUtil.splitDeptCode(filterMenuDataTypeIds);

		}else {
			ids = Arrays.asList(filterMenuDataTypeIds.split(","));
		}
		List<EwsUser> ewsUserList = userService.getUserListByTypeAndTypeIds(filterMenuDataType, ids, null);
		List<String> userIds = ewsUserList.stream().map(ewsUser -> ewsUser.getId()).collect(Collectors.toList());
		return userIds;
	}

	/**
	 * @Author: Donny_dong
	 * @Description: 判断子工单展示是否启用
	 * @Param:
	 * @return:
	 * @Date: 2020/11/2
	 */
	private StringBuffer getSysDictStatus(String dictCode,StringBuffer sql) {
		SysDict sysDict = new SysDict();
		sysDict.setDictCode(dictCode);
		SysDictVo sysDictVo = sysDictDao.selectOneByAny(sysDict);
		String status = null;
		if (Objects.nonNull(sysDictVo)) {
			status = sysDictVo.getStatus();
		}
		if (StringUtils.isEmpty(status)){
			status = ConstantCode.STATUS_CLOSED;
		}
		if(ConstantCode.STATUS_CLOSED.equals(status)){
			sql.append(" and b.is_children = 0");
		}
		return sql;
	}

	/**
	 * TODO 获取和当前登录人部同门的所有用户
	 * @author: victor_tang
	 * @createtime: 2020/5/8 19:00
	 * @param userId 用户id
	 * @return: java.utils.List<java.lang.String>
	 */
	private List<String> getAllUserWithSameDept(String userId) {
		EwsUser ewsUser = ewsUserDao.selectById(userId);
		HashMap<String, Object> hashMap = new HashMap<>(4);
		ArrayList<String> list = new ArrayList<>();
		list.add(ewsUser.getDepartmentId());
		hashMap.put("tenantId", ewsUser.getTenantId());
		hashMap.put("departmentIds", list);
		List<EwsUser> ewsUsers = ewsUserDao.selectAllUserList(hashMap);
		List<String> allIdList = ewsUsers.stream().map(e -> e.getId()).collect(Collectors.toList());
		return allIdList;
	}

	/**
	 * TODO 拼接sql条件
	 * @author: victor_tang
	 * @createtime: 2020/5/9 10:49
	 * @param jsonObject 条件数据
	 * @param sql sql片段
	 * @param userId 用户id
	 * @param sqlRelation sql拼接关系关键字
	 * @return: java.lang.String
	 */
	public String joinSqlCondition(JSONObject jsonObject,String sql, String userId,
											 String sqlRelation) {
		if (jsonObject.isEmpty()) {
			return sql;
		}
		for (int i = 0; i < jsonObject.getJSONArray("condition").size(); i++) {
			// 条件json对象
			JSONObject conditionJsonObj =jsonObject.getJSONArray("condition").getJSONObject(i);
			//第一级过滤条件主键（有相应表）
			Integer firstId = conditionJsonObj.getJSONObject("firstId").getInteger("value");
			//第二级过滤条件 是 包含等
			Integer secondDataId = conditionJsonObj.getJSONObject("secondDataId").getInteger("value");

			// 获取查询关系字段（后期改造为前端传递，不再请求数据库查询）
			FilterFirst filterFirst = filterFirstDao.selectById(firstId);
			Integer filterSecondId = filterFirst.getFilterSecondId();//对应的二级条件 是 否 包含等
			String relationFieldCode = filterFirst.getRelationFieldCode();
			String belongToTable = filterFirst.getBelongToTable();

			// 给数据库字段重命名
			if ("work_order".equals(belongToTable)) {
				relationFieldCode = "a."+relationFieldCode;
			}else if("work_order_relation".equals(belongToTable)){
				relationFieldCode = "b."+relationFieldCode;
			}

			// 设置默认字段存储模式为单独存储
			String fieldSaveMode = ConstantCode.FieldSaveMode.SINGLE;

			//1.二级筛选条件为（包含，不包含）
			if (ConstantCode.FilterSecond.CONTAIN_ID_ONE==filterSecondId) {
				// 逗号分割的关注人id，特殊处理
				if (ConstantCode.FilterConditionField.WORK_ORDER_MONITOR_ID.equals(relationFieldCode)) {
					fieldSaveMode = ConstantCode.FieldSaveMode.UNION;
				}
				//二级筛选条件为（包含，不包含）
				sql =  joinReferToContainsSql(conditionJsonObj, secondDataId,
						sql, relationFieldCode, sqlRelation,fieldSaveMode,userId);
			}
			//2.二级筛选条件为(是，包含，包含任意，任意不包含)
			else if (ConstantCode.FilterSecond.YES_ID_TWO==filterSecondId) {
				sql =  joinReferToAnyContainsSql(conditionJsonObj,secondDataId, sql,
						relationFieldCode,sqlRelation);
			}
			//3.二级筛选条件涉及到人且条件为(是，不是，包含，不包含)
			else if (ConstantCode.FilterSecond.USER_YES_ID_THREE==filterSecondId) {
				// 逗号分割的当前处理人，历史处理人，特殊处理
				if (ConstantCode.FilterConditionField.CURRENT_HANDLER_ID.equals(relationFieldCode)
				|| ConstantCode.FilterConditionField.HISTORY_HANDLER_ID.equals(relationFieldCode)
				) {
					fieldSaveMode = ConstantCode.FieldSaveMode.UNION;
				}
				sql =  joinReferToPeopleSql(conditionJsonObj, secondDataId, sql,
						relationFieldCode, sqlRelation,fieldSaveMode, userId);
			}
			//4.二级筛选条件涉及到日期且(早于，晚于，介于)
			else if (ConstantCode.FilterSecond.BEFORE_ID_FOUR==filterSecondId) {
				sql =  joinReferToDateSql(conditionJsonObj, secondDataId, sql,
						relationFieldCode, sqlRelation);
			}
			//5.二级筛选条件为(大于，小于，等于)
			else if (ConstantCode.FilterSecond.EQUAL_ID_FIVE==filterSecondId) {
				sql =  joinReferToCompareTheSizeSql(conditionJsonObj, secondDataId,
						sql, relationFieldCode,sqlRelation);
			}
			//6.二级筛选条件涉及到时间长度且(大于，小于，介于)
			else if (ConstantCode.FilterSecond.BETWEEN_ID_SIX==filterSecondId) {
				sql =  joinReferToLengthOfTimeSql(conditionJsonObj, secondDataId,
						sql,relationFieldCode, sqlRelation);
			}
		}
		return sql;
	}
	/**
	 * TODO 拼接满足所有条件的涉及比较大小的sql(大于，小于，等于)
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToCompareTheSizeSql(JSONObject conditionJsonObj,
											 int secondDataId, String sql,
											 String fieldName,String sqlRelation) {
		Integer num = conditionJsonObj.getInteger("value");
		// 1为等于
		if (1 == secondDataId) {
			sql += " " + sqlRelation + " " + fieldName + " = " + num;
		}
		// 2为大于
		else if (2 == secondDataId) {
			sql += " " + sqlRelation + " " + fieldName + " > " + num;
		}
		// 3为小于
		else {
			sql += " " + sqlRelation + " " + fieldName + " < " + num;
		}
		return sql;
	}
	/**
	 * TODO 拼接满足所有条件的涉及任意包含的sql(是，包含，包含任意，任意不包含)
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToAnyContainsSql(JSONObject conditionJsonObj,
										  int secondDataId, String sql,
										  String fieldName,String sqlRelation) {
		// 1为是
		if (1 == secondDataId) {
			String content = conditionJsonObj.getString("value");
			sql += " " + sqlRelation + " " + fieldName + " = '" + content + "'";
		}
		// 2为不是
		else if (2 == secondDataId) {
			String content = conditionJsonObj.getString("value");
			sql += " " + sqlRelation + " " + fieldName + " != '" + content +"'";
		}
		// 3为包含任意
		else if (3 == secondDataId) {
			List list = conditionJsonObj.getObject("value",List.class);
			String content = null;
			if (null != list) {
				for (int j = 0; j < list.size(); j++) {
					if (j == 0) {
						content = "'"+list.get(j)+"'";
					} else {
						content += ",'" + list.get(j)+"'";
					}
				}
			}
			sql += " " + sqlRelation + " " + fieldName + " in (" + content +
					")";
		}
		// 4为不包含任意
		else {
			List list = conditionJsonObj.getObject("value",List.class);
			String content = null;
			if (null != list) {
				for (int j = 0; j < list.size(); j++) {
					if (j == 0) {
						content = "'"+list.get(j)+"'";
					} else {
						content += ",'" + list.get(j)+"'";
					}
				}
			}
			sql += " " + sqlRelation + " " + fieldName + " not in (" + content +
					")";
		}
		return sql;
	}

	/**
	 * TODO 拼接满足所有条件的涉及字段包含不包含的sql(包含，不包含)
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToContainsSql(JSONObject conditionJsonObj,
										  int secondDataId, String sql,
										  String fieldName,String sqlRelation,
										  String fieldSaveMode,String userId) {
		// 独立字段处理
		if (ConstantCode.FieldSaveMode.SINGLE.equals(fieldSaveMode)) {
			return  joinSingleReferToContainsSql(conditionJsonObj,secondDataId,
					sql,fieldName,sqlRelation);
		}
		// 逗号分割联合字段处理
		else if (ConstantCode.FieldSaveMode.UNION.equals(fieldSaveMode)) {
			return  joinUnionReferToContainsSql(conditionJsonObj,secondDataId,
					sql,fieldName,sqlRelation,userId);
		}
		return sql;
	}
	/**
	 * TODO 涉及逗号分割联合存储字段包含不包含的sql
	 * @author: victor_tang
	 * @createtime: 2020/5/9 10:01
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @param userId 用户id
	 * @return: java.lang.String
	 */
	private String joinUnionReferToContainsSql(JSONObject conditionJsonObj,
											  int secondDataId, String sql, String fieldName, String sqlRelation, String userId) {
		String content = conditionJsonObj.getString("value");
		if (ConstantCode.FilterConditionField.WORK_ORDER_MONITOR_ID.equals(fieldName)) {
			// 工单关注人特殊处理
			content = userId;
		}
		// 1为包含 2为不包含
		if (1 == secondDataId) {
			sql += " "+sqlRelation+" find_in_set('" + content + "',"+fieldName+")";
		} else {
			sql += " "+sqlRelation+" !find_in_set('" + content + "',"+fieldName+")";
		}
		return sql;
	}


	/**
	 * TODO 涉及独立存储字段包含不包含的sql
	 * @author: victor_tang
	 * @createtime: 2020/5/9 9:47
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @return: java.lang.String
	 */
	private String joinSingleReferToContainsSql(JSONObject conditionJsonObj, int secondDataId, String sql, String fieldName, String sqlRelation) {
		String content = conditionJsonObj.getString("value");
		if (1 == secondDataId) {
			sql += " " + sqlRelation + " " + fieldName + " like ('%" + content +"%')";
		} else {
			sql += " " + sqlRelation + " " + fieldName + " not like ('%" + content + "%')";
		}
		return sql;
	}

	/**
	 * TODO 拼接满足所有条件的涉及时间长度大小的sql(大于，小于，介于)
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToLengthOfTimeSql(JSONObject conditionJsonObj,
											  int secondDataId, String sql,
											  String fieldName, String sqlRelation) {
		if (1 == secondDataId) {
			// 大于
			Double time = conditionJsonObj.getDouble("value");
			// 将小时转换为毫秒值
			long times = new BigDecimal(time).multiply(new BigDecimal(60 * 60 * 1000))
					.setScale(0, BigDecimal.ROUND_UP).longValue();
			String dateTime = DateHandleUtil.getFormatDateTimeByTimes(System.currentTimeMillis() - times);
			sql += " " + sqlRelation + " " + fieldName + " < '" + dateTime + "'";
		}
		// 小于
		else if (2 == secondDataId) {
			Double time = conditionJsonObj.getDouble("value");
			// 将小时转换为毫秒值
			long times = new BigDecimal(time).multiply(new BigDecimal(60 * 60 * 1000))
					.setScale(0, BigDecimal.ROUND_UP).longValue();
			String dateTime = DateHandleUtil.getFormatDateTimeByTimes(System.currentTimeMillis() - times);
			sql += " " + sqlRelation + " " + fieldName + " > '" + dateTime + "'";
		}
		// 介于
		else {
			Double time1 = 0d;
			Double time2 = 0d;
			List list = conditionJsonObj.getObject("value", List.class);
			if (null != list) {
				for (int j = 0; j < list.size(); j++) {
					if (0 == j) {
						// 小
						Object ob = list.get(j);
						time1 = Double.parseDouble(ob.toString());
					}
					if (1 == j) {
						// 大
						Object ob = list.get(j);
						time2 = Double.parseDouble(ob.toString());
					}
				}
			}
			// 将小时转换为毫秒值
			long times1 =
					new BigDecimal(time1).multiply(new BigDecimal(60 * 60 * 1000))
							.setScale(0, BigDecimal.ROUND_UP).longValue();
			String dateTime1 =
					DateHandleUtil.getFormatDateTimeByTimes(System.currentTimeMillis() - times1);

			long times2 =
					new BigDecimal(time2).multiply(new BigDecimal(60 * 60 * 1000))
							.setScale(0, BigDecimal.ROUND_UP).longValue();
			String dateTime2 =
					DateHandleUtil.getFormatDateTimeByTimes(System.currentTimeMillis() - times2);

			sql += " " + sqlRelation + " " + fieldName + " BETWEEN '" + dateTime1 + "' " +
					"and '" + dateTime2 + "'";
		}
		return sql;
	}

	/**
	 * TODO 拼接满足所有条件的涉及时间点和时间段的sql(早于，晚于，介于)
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToDateSql(JSONObject conditionJsonObj,
									  int secondDataId, String sql,
									  String fieldName, String sqlRelation) {
		// 早于
		if (1 == secondDataId) {
			String date = conditionJsonObj.getString("value");
			sql += " " + sqlRelation + " " + fieldName + "<= '" + date + "'";
		}
		// 晚于
		else if (2 == secondDataId) {
			String date = conditionJsonObj.getString("value");
			sql += " " + sqlRelation + " " + fieldName + ">= '" + date + "'";
		}
		// 介于
		else {
			List list = conditionJsonObj.getObject("value", List.class);
			String createTime1 = null;
			String createTime2 = null;
			if (null != list) {
				for (int j = 0; j < list.size(); j++) {
					if (0 == j) {
						// 小
						createTime1 = list.get(j).toString();
					}
					if (1 == j) {
						// 大
						createTime2 = list.get(1).toString();
					}
				}
			}
			sql += " " + sqlRelation + " " + fieldName + " BETWEEN '" + createTime1 + "' " +
					"and '" + createTime2 + "'";
		}
		return sql;
	}

	/**
	 * TODO 拼接满足所有条件的涉及人的sql（是，不是，包含，不包含）
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @param fieldSaveMode    字段存储模式   单独single  逗号分割的联合字段union
	 * @param userId           当前登录人id
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:11
	 * @return: java.lang.String
	 */
	private String joinReferToPeopleSql(JSONObject conditionJsonObj, int secondDataId, String sql,
										String fieldName, String sqlRelation,
										String fieldSaveMode, String userId) {
		if (ConstantCode.FieldSaveMode.SINGLE.equals(fieldSaveMode)) {
			return joinSingleFieldSql(conditionJsonObj, secondDataId, sql,
					fieldName, sqlRelation, userId);
		} else if (ConstantCode.FieldSaveMode.UNION.equals(fieldSaveMode)) {
			return joinUnionFieldSql(conditionJsonObj, secondDataId, sql,
					fieldName, sqlRelation, userId);
		}
		return sql;
	}

	/**
	 * TODO 拼接独立存储模式人的sql片段
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @param userId           当前登录人id
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:45
	 * @return: java.lang.String
	 */
	private String joinSingleFieldSql(JSONObject conditionJsonObj,
									  int secondDataId, String sql, String fieldName, String sqlRelation, String userId) {
		String secondType = conditionJsonObj.getJSONObject("typeId").getString("value");
		// 用户
		if (ConstantCode.UserRoleDeptType.USER == secondType) {
			// 是
			if (1 == secondDataId) {
				String createUserId = conditionJsonObj.getString("value");
				sql += " " + sqlRelation + " " + fieldName + "=" + createUserId;
			} else if (2 == secondDataId) {
				// 不是
				String createUserId = conditionJsonObj.getString("value");
				sql += " " + sqlRelation + " " + fieldName + "!=" + createUserId;
			} else if (3 == secondDataId) {
				// 包含
				String createUserIds = getUserIdsSqlByUserList(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " in (" + createUserIds + ")";
			} else {
				// 不包含
				String createUserIds = getUserIdsSqlByUserList(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " not in (" + createUserIds + ")";
			}
		}
		// 角色
		else if (ConstantCode.UserRoleDeptType.ROLE == secondType) {
			if (1 == secondDataId) {
				// 是
				String userIds = getUserIdsSqlByRoleId(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " in(" + userIds + ")";
			} else if (2 == secondDataId) {
				// 不是
				String userIds = getUserIdsSqlByRoleId(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " not in(" + userIds + ")";
			} else if (3 == secondDataId) {
				// 包含
				String userIds = getUserIdsSqlByRoleIds(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " in(" + userIds + ")";
			} else if (4 == secondDataId) {
				// 不包含
				String userIds = getUserIdsSqlByRoleIds(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " not in(" + userIds + ")";
			}
		}
		// 部门
		else if (ConstantCode.UserRoleDeptType.DEPT.equals(secondType)) {
			if (1 == secondDataId) {
				// 是
				String userIds = getUserIdsSqlByDeptId(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " in(" + userIds + ")";
			} else if (2 == secondDataId) {
				// 不是
				String userIds = getUserIdsSqlByDeptId(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " not in(" + userIds + ")";
			} else if (3 == secondDataId) {
				// 包含
				String userIds = getUserIdsSqlByDeptIdList(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " in(" + userIds + ")";
			} else if (4 == secondDataId) {
				// 不包含
				String userIds = getUserIdsSqlByDeptIdList(conditionJsonObj);
				sql += " " + sqlRelation + " " + fieldName + " not in(" + userIds + ")";
			}
		}
		return sql;
	}

	/**
	 * TODO 拼接逗号分割联合存储模式人的sql片段
	 *
	 * @param conditionJsonObj 筛选条件对象
	 * @param secondDataId     二级过滤器id
	 * @param sql              原始sql
	 * @param fieldName        查询数据库字段名称
	 * @param sqlRelation      最外层sql关系   and或者or
	 * @param userId           当前登录人id
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:45
	 * @return: java.lang.String
	 */
	private String joinUnionFieldSql(JSONObject conditionJsonObj,
									 int secondDataId, String sql,
									 String fieldName, String sqlRelation,
									 String userId) {
		String secondType = conditionJsonObj.getJSONObject("typeId").getString("value");
		// 用户
		if (ConstantCode.UserRoleDeptType.USER.equals(secondType)) {
			// 是
			if (1 == secondDataId) {
				String handler = conditionJsonObj.getString("value");
				if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY.equals(handler)) {
					sql += " " + sqlRelation + " find_in_set('" + userId + "'," + fieldName + ")";
				} else {
					sql += " " + sqlRelation + " find_in_set('" + handler + "'," + fieldName + ")";
				}
			}
			// 不是
			else if (2 == secondDataId) {
				String handler = conditionJsonObj.getString("value");
				if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY.equals(handler)) {
					sql += " " + sqlRelation + " !find_in_set('" + userId + "'," + fieldName + ")";
				} else {
					sql += " " + sqlRelation + " !find_in_set('" + handler + "'," + fieldName + ")";
				}
			}
			// 包含
			else if (3 == secondDataId) {
				List list = conditionJsonObj.getObject("value", List.class);
				String handlerd = StringUtils.join(list, ",");
				if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY.equals(handlerd) && list.size() ==1) {
					sql += " " + sqlRelation + " " + "find_in_set('" + userId + "'," + fieldName + ")";
				} else {
					if(list.contains(ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY)){
						list.add(userId);
					}
					StringBuffer stringBuffer = new StringBuffer();
					String string = null;
					for (Object s:list
						 ) {
						stringBuffer.append("'").append(s).append("'").append(",");
						string = stringBuffer.toString();
						int i = string.lastIndexOf(",");
						string = string.substring(0,i);
					}

						sql += " " + sqlRelation + " " +  " INTE_ARRAY" +
								"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";

				}

			}
			// 不包含
			else if (4 == secondDataId) {
//				List list = conditionJsonObj.getObject("value", List.class);
//				String handlerd = StringUtils.join(list, ",");
//				sql += " " + sqlRelation + " !" + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				List list = conditionJsonObj.getObject("value", List.class);
				String handlerd = StringUtils.join(list, ",");
				if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY.equals(handlerd) && list.size() ==1) {
					sql += " " + sqlRelation + " " + "!find_in_set('" + userId + "'," + fieldName + ")";
				} else {
					if(list.contains(ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_KEY)){
						list.add(userId);
					}

					String string = getUserIdsForSql(list);
					sql += " " + sqlRelation + " " +  " !INTE_ARRAY" +
							"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";

				}
			}
		}
		// 角色
		else if (ConstantCode.UserRoleDeptType.ROLE .equals(secondType) ) {
			if (1 == secondDataId) {
				// 是
				List userIds = getUserIdListSqlByRoleId(conditionJsonObj,userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " " + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (2 == secondDataId) {
				// 不是
				List userIds = getUserIdListSqlByRoleId(conditionJsonObj, userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " !" + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " !INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (3 == secondDataId) {
				// 包含
				List userIds = getUserIdListSqlByRoleIds(conditionJsonObj,userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " " + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (4 == secondDataId) {
				// 不包含
				List userIds = getUserIdListSqlByRoleIds(conditionJsonObj, userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " !" + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " !INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			}
		}
		// 部门
		else if (ConstantCode.UserRoleDeptType.DEPT.equals(secondType)) {
			if (1 == secondDataId) {
				// 是
				List userIds = getUserIdListByDeptId(conditionJsonObj,userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " " + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (2 == secondDataId) {
				// 不是
				List userIds = getUserIdListByDeptId(conditionJsonObj, userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " !" + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " !INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (3 == secondDataId) {
				// 包含
				List userIds = getUserIdListByDeptIdList(conditionJsonObj,userId);
//				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " " + fieldName + " REGEXP replace" +
//						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			} else if (4 == secondDataId) {
				// 不包含
				List userIds = getUserIdListByDeptIdList(conditionJsonObj, userId);
				String handlerd = StringUtils.join(userIds, ",");
//				sql += " " + sqlRelation + " !" + fieldName + " REGEXP replace" +
////						"(" + handlerd + "," + "','" + "," + "'|'" + ")";
				String string = getUserIdsForSql(userIds);
				sql += " " + sqlRelation + " " +  " !INTE_ARRAY" +
						"(" + fieldName +","+"(SELECT GROUP_CONCAT(id separator',') FROM ews_user " + "where id in ("+string+"))" +")";
			}
		}
		return sql;
	}

	private String getUserIdsForSql(List list) {
		StringBuilder stringBuffer = new StringBuilder();
		String string = null;
		if (CollectionUtils.isEmpty(list)){
			return string;
		}
		for (Object s:list
		) {
			stringBuffer.append("'").append(s).append("'").append(",");
			string = stringBuffer.toString();
			int i = string.lastIndexOf(",");
			string = string.substring(0,i);
		}
		return string;
	}


	/**
	 * TODO 通过角色idList获取用户idsql拼接片段
	 *
	 * @param conditionJsonObj 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private String getUserIdsSqlByRoleIds(JSONObject conditionJsonObj) {
		// 包含
		List list = conditionJsonObj.getObject("value", List.class);
		List<EwsUserRole> userList = ewsUserRoleDao.selectByInRoles(list);
		return joinUserIdsSqlByEwsUserRole(userList);
	}

	/**
	 * TODO 通过角色id获取用户idsql拼接片段
	 *
	 * @param conditionJsonObj 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private String getUserIdsSqlByRoleId(JSONObject conditionJsonObj) {
		// 包含
		String roleId = conditionJsonObj.getString("value");
		List<EwsUserRole> userList = ewsUserRoleDao.selectByRoleId(roleId);
		return joinUserIdsSqlByEwsUserRole(userList);
	}

	/**
	 * TODO 通过角色id获取用户idlist
	 *
	 * @param conditionJsonObj 1
	 * @param userId
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private List<String> getUserIdListSqlByRoleId(JSONObject conditionJsonObj, String userId) {
		// 包含
		String roleId = conditionJsonObj.getString("value");
		List<EwsUserRole> userList = null;
		if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_ROLE_KEY.equals(roleId)) {
			List<EwsRole> ewsRoles = ewsUserRoleDao.selectRoleByUserId(userId);
			List<String> roleIds = null;
			if (!CollectionUtils.isEmpty(ewsRoles)) {
				roleIds = ewsRoles.stream().map(ewsRole -> ewsRole.getId()).collect(Collectors.toList());
				userList = ewsUserRoleDao.selectByInRoles(roleIds);
			}

		}else {
			userList = ewsUserRoleDao.selectByRoleId(roleId);
		}
		if (CollectionUtils.isEmpty(userList)) {
			return null;
		}
		return userList.stream().map(e -> e.getUserId()).collect(Collectors.toList());
	}

	/**
	 * TODO 通过角色idlist获取用户idlist
	 *
	 * @param conditionJsonObj 1
	 * @param userId
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private List<String> getUserIdListSqlByRoleIds(JSONObject conditionJsonObj, String userId) {
		// 包含
		List roleIdList = conditionJsonObj.getObject("value", List.class);
		if (roleIdList.contains(ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_ROLE_KEY)){
			List<EwsRole> ewsRoles = ewsUserRoleDao.selectRoleByUserId(userId);
			List<String> collect = null;
			if (!CollectionUtils.isEmpty(ewsRoles)) {
				collect = ewsRoles.stream().map(ewsRole -> ewsRole.getId()).collect(Collectors.toList());
				roleIdList.addAll(collect);
			}
		}
		List<EwsUserRole> userList = ewsUserRoleDao.selectByInRoles(roleIdList);
		if (CollectionUtils.isEmpty(userList)) {
			return null;
		}
		return userList.stream().map(e -> e.getUserId()).collect(Collectors.toList());
	}

	/**
	 * TODO 通过部门idList获取用户idsql拼接片段
	 *
	 * @param conditionJsonObj 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private String getUserIdsSqlByDeptIdList(JSONObject conditionJsonObj) {
		List list = conditionJsonObj.getObject("value", List.class);
		Map<String, Object> searchMap = new HashMap<>(2);
		searchMap.put("departmentIds", list);
		List<EwsUser> userList = ewsUserDao.selectAllUserList(searchMap);
		return joinUserIdsSql(userList);
	}

	/**
	 * TODO 通过部门id获取用户idsql拼接片段
	 *
	 * @param conditionJsonObj 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private String getUserIdsSqlByDeptId(JSONObject conditionJsonObj) {
		String deptId = conditionJsonObj.getString("value");
		Map<String, Object> searchMap = new HashMap<>(2);
		searchMap.put("departmentId", deptId);
		List<EwsUser> userList = ewsUserDao.selectAllUserList(searchMap);
		return joinUserIdsSql(userList);
	}

	/**
	 * TODO 通过部门id获取用户ids
	 *
	 * @param conditionJsonObj 1
	 * @param userId
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private List<String> getUserIdListByDeptId(JSONObject conditionJsonObj, String userId) {
		List list = conditionJsonObj.getObject("value", List.class);
		Object deptId = list.get(0);
		if (ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_DEPT_KEY.equals(deptId)){
			EwsUser ewsUser = ewsUserDao.selectById(userId);
			deptId = ewsUser.getDepartmentId();

		}else {
			deptId = list.get(list.size() - 1);
		}
		Map<String, Object> searchMap = new HashMap<>(2);
		searchMap.put("departmentId", deptId);
		List<EwsUser> userList = ewsUserDao.selectAllUserList(searchMap);
		if (CollectionUtils.isEmpty(userList)) {
			return null;
		}
		return userList.stream().map(e -> e.getId()).collect(Collectors.toList());
	}

	/**
	 * TODO 通过部门idList获取用户idList
	 *
	 * @param conditionJsonObj 1
	 * @param userId
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private List<String> getUserIdListByDeptIdList(JSONObject conditionJsonObj, String userId) {
		List<List<String>> listDouble = conditionJsonObj.getObject("value", List.class);
		List<String> deptIds = new ArrayList<>();
		List<String> currentLoadUserDept = new ArrayList<String>();
		currentLoadUserDept.add(ConstantCode.CurrentLoadUser.CURRENT_LOAD_USER_DEPT_KEY);
		if (listDouble.containsAll(currentLoadUserDept)){
			EwsUser ewsUser = ewsUserDao.selectById(userId);
			deptIds.add(ewsUser.getDepartmentId());
		}
		for (List<String> l:listDouble){
			deptIds.add(l.get(l.size()-1));
		}
		Map<String, Object> searchMap = new HashMap<>(2);
		searchMap.put("departmentIds", deptIds);
		List<EwsUser> userList = ewsUserDao.selectAllUserList(searchMap);
		if (CollectionUtils.isEmpty(userList)) {
			return null;
		}
		return userList.stream().map(e -> e.getId()).collect(Collectors.toList());
	}

	/**
	 * TODO 拼接用户idsql
	 *
	 * @param userList 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:06
	 * @return: java.lang.String
	 */
	String joinUserIdsSql(List<EwsUser> userList) {
		String userIds = null;
		if (null != userList) {
			for (int j = 0; j < userList.size(); j++) {
				if (j == 0) {
					userIds = "'" + userList.get(j).getId() + "'";
				} else {
					userIds += "," + "'" + userList.get(j).getId() + "'";
				}
			}
		}
		return userIds;
	}
	/**
	 * TODO 拼接用户idsql
	 *
	 * @param userList 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 14:06
	 * @return: java.lang.String
	 */
	String joinUserIdsSqlByEwsUserRole(List<EwsUserRole> userList) {
		String userIds = null;
		if (null != userList) {
			for (int j = 0; j < userList.size(); j++) {
				if (j == 0) {
					userIds = "'" + userList.get(j).getUserId() + "'";
				} else {
					userIds += "," + "'" + userList.get(j).getUserId() + "'";
				}
			}
		}
		return userIds;
	}
	/**
	 * TODO 通过用户列表获取用户idsql拼接片段
	 *
	 * @param conditionJsonObj 1
	 * @author: victor_tang
	 * @createtime: 2020/5/8 13:24
	 * @return: java.lang.String
	 */
	private String getUserIdsSqlByUserList(JSONObject conditionJsonObj) {
		List list = conditionJsonObj.getObject("value", List.class);
		String createUserIds = null;
		if (null != list) {
			for (int j = 0; j < list.size(); j++) {
				if (j == 0) {
					createUserIds = "'" + list.get(j) + "'";
				} else {
					createUserIds += "," + "'" + list.get(j) + "'";
				}
			}
		}
		return createUserIds;
	}
}
